﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using ADA.Entity;
using System.Data;
using System.Data.SqlClient;

namespace ADA.Data
{
    public class DALRice
    {
        //private string conStr = ConfigurationManager.AppSettings["connectionString"];
        private string conStr = Common.CONNECTION_STRING;

        public DataTable getAllRice()
        {
            DataTable tblRiceInfo = new DataTable();

            string sql = @"SELECT   [Rice].[ID],
                                    [Rice].[TypeName],
                                    [Rice].[Origin],
                                    [Admin].[Name]
                            FROM    [Rice], [Admin]
                            WHERE   [Rice].[AdminID]=[Admin].[ID]";

            SqlConnection conn = new SqlConnection(conStr);
            SqlCommand cmd = new SqlCommand(sql, conn);

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(tblRiceInfo);
            return tblRiceInfo;
        }

        public DataTable getAllRicePrice()
        {
            DataTable tblRicePriceInfo = new DataTable();

            string sql = @"SELECT   [Rice].[ID],
                                    [Rice].[TypeName],
                                    [Rice].[OldPrice],
                                    [Rice].[CurrentPrice],
                                    [Rice].[PercentPrice],
                                    [Rice].[CurrentDate]
                            FROM    [Rice]";

            SqlConnection conn = new SqlConnection(conStr);
            SqlCommand cmd = new SqlCommand(sql, conn);

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(tblRicePriceInfo);
            return tblRicePriceInfo;
        }

        public void updatePoster(short oldAdminID, short newAdminID)
        {
            string sql = @"UPDATE   [Rice]
                            SET     [Rice].[AdminID] = @newAdminID 
                            WHERE   [Rice].[AdminID] = @oldAdminID";

            SqlConnection conn = new SqlConnection(conStr);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.Parameters.Add("@oldAdminID", SqlDbType.TinyInt).Value = oldAdminID;
            cmd.Parameters.Add("@newAdminID", SqlDbType.TinyInt).Value = newAdminID;

            cmd.ExecuteNonQuery();
            conn.Close();
        }

        public void updatePrice(int riceID ,int newPrice)
        {
            string sql = @"UPDATE   [Rice]
                            SET     [Rice].[OldPrice] = [Rice].[CurrentPrice],
                                    [Rice].[CurrentPrice] = @newPrice,
                                    [Rice].[CurrentDate] = @currentDate
                            WHERE   [Rice].[ID] = @riceID";

            SqlConnection conn = new SqlConnection(conStr);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.Parameters.Add("@riceID", SqlDbType.Int).Value = riceID;
            cmd.Parameters.Add("@newPrice", SqlDbType.Int).Value = newPrice;
            cmd.Parameters.Add("@currentDate", SqlDbType.Date).Value = DateTime.Today;

            cmd.ExecuteNonQuery();
            conn.Close();
        }

        public void updatePercent(int riceID, int newPercent)
        {
            string sql = @"UPDATE   [Rice]
                            SET     [Rice].[PercentPrice] = @newPercent
                            WHERE   [Rice].[ID] = @riceID";

            SqlConnection conn = new SqlConnection(conStr);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.Parameters.Add("@riceID", SqlDbType.Int).Value = riceID;
            cmd.Parameters.Add("@newPercent", SqlDbType.Int).Value = newPercent;

            cmd.ExecuteNonQuery();
            conn.Close();
        }

        public Rice getRiceInfo(int riceID)
        {
            Rice rice = new Rice();
            DataTable tblRiceInfo = new DataTable();

            string sql = @"SELECT   [Rice].[TypeName],
                                    [Rice].[TypeNameJP],
                                    [Rice].[Image],
                                    [Rice].[Origin],
                                    [Rice].[OriginJP],
                                    [Rice].[Description],
                                    [Rice].[DescriptionJP]
                            FROM    [Rice]
                            WHERE [Rice].[ID] = @riceID";

            SqlConnection conn = new SqlConnection(conStr);
            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.Parameters.Add("@riceID", SqlDbType.Int).Value = riceID;

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(tblRiceInfo);

            if (tblRiceInfo.Rows.Count > 0)
            {
                rice.ID = riceID;
                rice.Name = Convert.ToString(tblRiceInfo.Rows[0]["TypeName"]).Trim();
                rice.NameJP = Convert.ToString(tblRiceInfo.Rows[0]["TypeNameJP"]).Trim();
                rice.Image = Convert.ToString(tblRiceInfo.Rows[0]["Image"]).Trim();
                rice.Origin = Convert.ToString(tblRiceInfo.Rows[0]["Origin"]).Trim();
                rice.OriginJP = Convert.ToString(tblRiceInfo.Rows[0]["OriginJP"]).Trim();
                rice.Description = Convert.ToString(tblRiceInfo.Rows[0]["Description"]).Trim();
                rice.DescriptionJP = Convert.ToString(tblRiceInfo.Rows[0]["DescriptionJP"]).Trim();
            }

            return rice;
        }

        public void insertRice(Rice rice)
        {
            string sql = @"INSERT INTO [Rice]
                                       ([TypeName]
                                       ,[TypeNameJP]
                                       ,[Image]
                                       ,[Description]
                                        ,[DescriptionJP]
                                       ,[Origin]
                                        ,[OriginJP]
                                       ,[CurrentDate]
                                       ,[CurrentPrice]
                                       ,[OldPrice]
                                       ,[PercentPrice]
                                       ,[AdminID])
                                 VALUES
                                       (@name
                                        ,@nameJP
                                       ,@image
                                       ,@description
                                        ,@descriptionJP
                                       ,@origin
                                        ,@originJP
                                       ,@currentDate
                                       ,0
                                       ,0
                                       ,0
                                       ,@adminID)";

            SqlConnection conn = new SqlConnection(conStr);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = rice.Name;
            cmd.Parameters.Add("@nameJP", SqlDbType.NVarChar).Value = rice.NameJP;
            cmd.Parameters.Add("@image", SqlDbType.VarChar).Value = rice.Image;
            cmd.Parameters.Add("@origin", SqlDbType.NVarChar).Value = rice.Origin;
            cmd.Parameters.Add("@originJP", SqlDbType.NVarChar).Value = rice.OriginJP;
            cmd.Parameters.Add("@description", SqlDbType.NVarChar).Value = rice.Description;
            cmd.Parameters.Add("@descriptionJP", SqlDbType.NVarChar).Value = rice.DescriptionJP;
            cmd.Parameters.Add("@currentDate", SqlDbType.Date).Value = DateTime.Today;
            cmd.Parameters.Add("@adminID", SqlDbType.TinyInt).Value = rice.AdminID;

            cmd.ExecuteNonQuery();
            conn.Close();
        }

        public void updateRice(Rice rice)
        {
            string sql = @"UPDATE   [Rice]
                            SET     [TypeName] = @name
                                    ,[TypeNameJP] = @nameJP
                                    ,[Image] = @image
                                    ,[Description] = @description
                                    ,[DescriptionJP] = @descriptionJP
                                    ,[Origin] = @origin
                                    ,[OriginJP] = @originJP
                                    ,[AdminID] = @adminID
                            WHERE   [Rice].[ID]=@riceID";

            SqlConnection conn = new SqlConnection(conStr);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.Parameters.Add("@riceID", SqlDbType.Int).Value = rice.ID;
            cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = rice.Name;
            cmd.Parameters.Add("@nameJP", SqlDbType.NVarChar).Value = rice.NameJP;
            cmd.Parameters.Add("@image", SqlDbType.VarChar).Value = rice.Image;
            cmd.Parameters.Add("@origin", SqlDbType.NVarChar).Value = rice.Origin;
            cmd.Parameters.Add("@originJP", SqlDbType.NVarChar).Value = rice.OriginJP;
            cmd.Parameters.Add("@description", SqlDbType.NVarChar).Value = rice.Description;
            cmd.Parameters.Add("@descriptionJP", SqlDbType.NVarChar).Value = rice.DescriptionJP;
            cmd.Parameters.Add("@adminID", SqlDbType.TinyInt).Value = rice.AdminID;

            cmd.ExecuteNonQuery();
            conn.Close();
        }

        public void deleteRice(int riceID)
        {
            string sql = @"DELETE FROM [Rice]
                                WHERE [Rice].[ID]=@riceID";

            SqlConnection conn = new SqlConnection(conStr);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.Parameters.Add("@riceID", SqlDbType.Int).Value = riceID;

            cmd.ExecuteNonQuery();
            conn.Close();
        }
    }
}
